#for using APIs
import requests
#data monipulation and analysis
import pandas as pd
# convert text
from io import StringIO
import math
#calculate distance between two points
from geopy.distance import geodesic
#interactive map
import folium
from collections import Counter
from branca.element import Template, MacroElement
import matplotlib.pyplot as plt
from sklearn.preprocessing import StandardScaler
from sklearn.cluster import KMeans

def API_Unlimited(dataset_id):
    base_url = 'https://data.melbourne.vic.gov.au/api/explore/v2.1/catalog/datasets/'
    #apikey = 'apikey
    dataset_id = dataset_id
    format = 'csv'

    url = f'{base_url}{dataset_id}/exports/{format}'
    params = {
        'select': '*',
        'limit': -1,
        'lang': 'en',
        'timezone': 'UTC',
    }
    response = requests.get(url, params=params)

    if response.status_code == 200:
        url_content = response.content.decode('utf-8')
        dataset = pd.read_csv(StringIO(url_content), delimiter=';')
        return dataset
    else:
        print(f'Request failed with status code {response.status_code}')

landmark_location = API_Unlimited('landmarks-and-places-of-interest-including-schools-theatres-health-services-spor')
parking_capacity = API_Unlimited('off-street-car-parks-with-capacity-and-type')
pedestrian_count = API_Unlimited('pedestrian-counting-system-monthly-counts-per-hour')
bus_stop = API_Unlimited('bus-stops')
tram_stop = API_Unlimited('tram-tracks')

bus_stop = API_Unlimited('bus-stops')
tram_stop = API_Unlimited('tram-tracks')

landmark_location.head()

parking_capacity.head()

pedestrian_count.head()

bus_stop.head()

tram_stop.head()

def filter_landmarks(data):
    #choose only heath services and edication centre theme
    themes = ["Health Services", "Education Centre"]
    filtered_theme = data[data['theme'].isin(themes)]

    #from 'Community Use' theme, include only libraries
    community_use = data[
        (data['theme'] == "Community Use") &
        (data['feature_name'].str.contains("library", case=False, na=False))
    ]

    filtered_landmarks = pd.concat([filtered_theme, community_use], ignore_index=True)
    return filtered_landmarks

filtered_landmarks = filter_landmarks(landmark_location)

#split 'co_ordinates' column into 'latitude' and 'longitude' columns
filtered_landmarks[['latitude', 'longitude']] = filtered_landmarks['co_ordinates'].str.split(',', expand=True)

#change string to float
filtered_landmarks['latitude'] = filtered_landmarks['latitude'].astype(float)
filtered_landmarks['longitude'] = filtered_landmarks['longitude'].astype(float)
filtered_landmarks = filtered_landmarks.drop(columns=['co_ordinates'])

#check missing values
print(filtered_landmarks.isnull().sum())

theme           0
sub_theme       0
feature_name    0
latitude        0
longitude       0
dtype: int64

filtered_landmarks.head()

#use the data only in 2023
parking_2023 = parking_capacity[parking_capacity['census_year'] == 2023]

#define types of parking which are public spaces
public_parking_types = [
    "Commercial",
    "Commercial, Private",
    "Commercial, Residential",
    "Commercial, Private, Residential"
]

#filter the data to includes only public parkings
filtered_parking = parking_2023[parking_2023['parking_type'].isin(public_parking_types)]

#group data by lcoation
filtered_parking = filtered_parking.groupby(['latitude', 'longitude', 'building_address', 'clue_small_area'], as_index=False).agg({
    'parking_spaces': 'sum',
    'parking_type': lambda x: ', '.join(sorted(set(x)))  
})

#check missing values
print(filtered_parking.isnull().sum())

latitude            0
longitude           0
building_address    0
clue_small_area     0
parking_spaces      0
parking_type        0
dtype: int64

filtered_parking.head()

#convert sensing_date to datetime format
pedestrian_count['sensing_date'] = pd.to_datetime(pedestrian_count['sensing_date'])

#filter data to include only records from 2024
filtered_pedestrian = pedestrian_count[pedestrian_count['sensing_date'].dt.year >= 2024].copy()
#split the 'location' column into separate latitude and longitude columns
filtered_pedestrian[['latitude', 'longitude']] = filtered_pedestrian['location'].str.split(',', expand=True)

#convert string to float
filtered_pedestrian['latitude'] = filtered_pedestrian['latitude'].astype(float)
filtered_pedestrian['longitude'] = filtered_pedestrian['longitude'].astype(float)
#drop unnecessary columns
filtered_pedestrian = filtered_pedestrian.drop(columns=['id', 'direction_1', 'direction_2', 'sensor_name', 'location'])

#check missing values
print(filtered_pedestrian.isnull().sum())

location_id        0
sensing_date       0
hourday            0
pedestriancount    0
latitude           0
longitude          0
dtype: int64

filtered_pedestrian.head()

#split 'geo_point_2d' into latitude and longitude
bus_stop[['latitude', 'longitude']] = bus_stop['geo_point_2d'].str.split(',', expand=True)

bus_stop['latitude'] = bus_stop['latitude'].astype(float)
bus_stop['longitude'] = bus_stop['longitude'].astype(float)

# drop unnecessary columns
columns_to_drop = ['geo_point_2d', 'geo_shape', 'prop_id', 'addresspt1', 'addressp_1', 
                   'asset_clas', 'asset_type', 'objectid', 'str_id', 'addresspt', 
                   'asset_subt', 'model_desc', 'mcc_id']

filtered_bus_stop = bus_stop.drop(columns=columns_to_drop).copy()

#check missing values
print(bus_stop.isnull().sum())

geo_point_2d      0
geo_shape         0
prop_id           0
addresspt1        0
addressp_1        0
asset_clas        0
asset_type        0
objectid          0
str_id            0
addresspt         0
asset_subt      309
model_desc        0
mcc_id            0
roadseg_id        0
descriptio        0
model_no          0
latitude          0
longitude         0
dtype: int64

bus_stop.head()

#split 'geo_point_2d' into latitude and longitude
tram_stop[['latitude', 'longitude']] = tram_stop['geo_point_2d'].str.split(',', expand=True)

tram_stop['latitude'] = tram_stop['latitude'].astype(float)
tram_stop['longitude'] = tram_stop['longitude'].astype(float)

# drop unnecessary columns
columns_to_drop = ['geo_point_2d', 'geo_shape', 'descriptio']

filtered_tram_stop = tram_stop.drop(columns=columns_to_drop).copy()

# check missing values
print(tram_stop.isnull().sum())

geo_point_2d    0
geo_shape       0
descriptio      0
name            0
latitude        0
longitude       0
dtype: int64

tram_stop.head()

landmark_parking = []

for _, landmark in filtered_landmarks.iterrows():
    landmark_location = (landmark['latitude'], landmark['longitude'])

    #find nearby car parks within 400 metres
    nearby_parks = filtered_parking[filtered_parking.apply(
        lambda x: geodesic(landmark_location, (x['latitude'], x['longitude'])).meters <= 400,
        axis=1
    )]

    total_spaces = nearby_parks['parking_spaces'].sum()
    carpark_count = len(nearby_parks)

    #address of nearby parking
    building_addresses = nearby_parks['building_address'].unique().tolist()

    landmark_parking.append({
        'feature_name': landmark['feature_name'],
        'theme': landmark['theme'],
        'latitude': landmark['latitude'],
        'longitude': landmark['longitude'],
        'nearby_public_carparks': carpark_count,
        'total_public_spaces': total_spaces,
        'parking_addresses': building_addresses
    })
#change to dataframe
landmark_parking_df = pd.DataFrame(landmark_parking)

landmark_parking_df.head()
#print(landmark_parking_df.iloc[2]['parking_addresses'])

landmark_pedestrian = []

for _, landmark in filtered_landmarks.iterrows():
    landmark_location = (landmark['latitude'], landmark['longitude'])

    # find nearby sensors
    nearby_sensors = filtered_pedestrian[filtered_pedestrian.apply(
        lambda x: geodesic(landmark_location, (x['latitude'], x['longitude'])).meters <= 400, axis=1)]

    # calculate average pedestrian count
    avg_ped = nearby_sensors['pedestriancount'].mean() if not nearby_sensors.empty else 0

    # calculate peak hour and pedestrian count during that hour
    if not nearby_sensors.empty:
        hourly_profile = nearby_sensors.groupby('hourday')['pedestriancount'].mean()
        peak_hour = int(hourly_profile.idxmax())
        peak_pedestrian_count = int(hourly_profile.max())
    else:
        peak_hour = 'no data'
        peak_pedestrian_count = 0

    landmark_pedestrian.append({
        'feature_name': landmark['feature_name'],
        'avg_pedestrian_count': round(avg_ped, 2),
        'peak_hour': peak_hour,
        'peak_pedestrian_count': peak_pedestrian_count
    })

landmark_pedestrian_df = pd.DataFrame(landmark_pedestrian)

#If no sensor was found near a landmark, the average pedestrian count was set to zero
landmark_pedestrian_df['pedestrian_display'] = landmark_pedestrian_df['avg_pedestrian_count'].apply(
    lambda x: 'no data' if x == 0 else x
)

landmark_pedestrian_df.head()

landmark_access_summary = landmark_parking_df.merge(landmark_pedestrian_df, on='feature_name', how='left')
landmark_access_summary.head()

landmark_access_summary.head(10)

all_parks = landmark_access_summary.explode('parking_addresses') 

#count how many times each address is linked to a landmark
shared_counts = all_parks['parking_addresses'].value_counts()
shared_parks = shared_counts[shared_counts > 1]

print("Shared car parks (used by more than one landmark):")
print(shared_parks)

Shared car parks (used by more than one landmark):
parking_addresses
Royal Womens Hospital 18-22 Flemington Road PARKVILLE VIC 3050                     6
Victorian Comprehensive Cancer Centre 301-327 Grattan Street MELBOURNE VIC 3000    5
Royal Melbourne Hospital 300-328 Grattan Street PARKVILLE VIC 3050                 5
Carpark Melbourne University 220 Berkeley Street CARLTON VIC 3053                  4
94-106 Grey Street EAST MELBOURNE VIC 3002                                         3
The Albert 158 Albert Street EAST MELBOURNE VIC 3002                               3
298-336 Victoria Parade EAST MELBOURNE VIC 3002                                    3
RMIT Village 5-17 Flemington Road NORTH MELBOURNE VIC 3051                         3
Kenneth Myer Building 144 30 Royal Parade PARKVILLE VIC 3010                       2
33-61 Cathedral Place EAST MELBOURNE VIC 3002                                      2
553 St Kilda Road MELBOURNE VIC 3004                                               2
6-30 Tyne Street CARLTON VIC 3053                                                  2
368-386 Lygon Street CARLTON VIC 3053                                              2
265-281 Faraday Street CARLTON VIC 3053                                            2
204-218 Lygon Street CARLTON VIC 3053                                              2
410-418 Albert Street EAST MELBOURNE VIC 3002                                      2
58-66 La Trobe Street MELBOURNE VIC 3000                                           2
QV Retail & Parking 221 Little Lonsdale Street MELBOURNE VIC 3000                  2
Melbourne Central 183-265 La Trobe Street MELBOURNE VIC 3000                       2
14-20 Blackwood Street NORTH MELBOURNE VIC 3051                                    2
Carlton Clocktower Complex 247-253 Drummond Street CARLTON VIC 3053                2
Alfred Hospital 23-99 Commercial Road PRAHRAN VIC 3181                             2
Name: count, dtype: int64

nearby_bus_counts = []

for _, landmark in filtered_landmarks.iterrows():
    landmark_location = (landmark['latitude'], landmark['longitude'])
    
    #find nearby bus stops within 400m
    nearby_buses = filtered_bus_stop[filtered_bus_stop.apply(
        lambda x: geodesic(landmark_location, (x['latitude'], x['longitude'])).meters <= 400,
        axis=1
    )]

    nearby_bus_counts.append({
        'feature_name': landmark['feature_name'],
        'nearby_bus_stops': len(nearby_buses)
    })

# Convert to DataFrame
nearby_bus_df = pd.DataFrame(nearby_bus_counts)

# Create a list to store results
nearby_tram_counts = []

for _, landmark in filtered_landmarks.iterrows():
    landmark_location = (landmark['latitude'], landmark['longitude'])
    
    # Find nearby tram tracks within 400m
    nearby_trams = filtered_tram_stop[filtered_tram_stop.apply(
        lambda x: geodesic(landmark_location, (x['latitude'], x['longitude'])).meters <= 400,
        axis=1
    )]

    nearby_tram_counts.append({
        'feature_name': landmark['feature_name'],
        'nearby_tram_tracks': len(nearby_trams)
    })

# Convert to DataFrame
nearby_tram_df = pd.DataFrame(nearby_tram_counts)

# Merge bus stops
landmark_access_summary = landmark_access_summary.merge(nearby_bus_df, on='feature_name', how='left')

# Merge tram tracks
landmark_access_summary = landmark_access_summary.merge(nearby_tram_df, on='feature_name', how='left')

# Check the result
landmark_access_summary.head()

shared_counts_dict = shared_counts.to_dict()

#shared_by = how many times each parking_address 
all_parks['shared_by'] = all_parks['parking_addresses'].map(shared_counts_dict)

#divide a landmark’s total spaces equally across its car parks
all_parks['shared_space'] = all_parks['total_public_spaces'] / all_parks.groupby('feature_name')['parking_addresses'].transform('count')

#adjust shared space based on how many landmarks use each car park
all_parks['adjusted_space'] = all_parks['shared_space'] / all_parks['shared_by']

#sum up adjusted spaces per landmark
adjusted_parking_summary = all_parks.groupby('feature_name', as_index=False)['adjusted_space'].sum()
adjusted_parking_summary.rename(columns={'adjusted_space': 'adjusted_total_public_spaces'}, inplace=True)

#merge back with original summary
landmark_access_summary = landmark_access_summary.merge(adjusted_parking_summary, on='feature_name', how='left')
landmark_access_summary.head()

all_parks.head(10)

# calculate parking per person during peak hour
landmark_access_summary['parking_per_peak_person'] = (
    landmark_access_summary['adjusted_total_public_spaces'] / (landmark_access_summary['peak_pedestrian_count'] + 1)
)

#demand risk level
def classify_risk(ratio):
    if ratio['peak_pedestrian_count'] == 0:
        return 'Unknown'
    elif ratio['parking_per_peak_person'] < 0.5:
        return 'High'
    elif ratio['parking_per_peak_person'] < 1.5:
        return 'Moderate'
    else:
        return 'Low'

landmark_access_summary['demand_risk'] = landmark_access_summary.apply(classify_risk, axis=1)

landmark_access_summary

landmark_access_summary[landmark_access_summary['demand_risk'] == 'High'].head()

#filter landmarks with sensor data
landmarks_with_sensor = landmark_access_summary[landmark_access_summary['peak_pedestrian_count'] > 0]

total_landmarks = len(landmarks_with_sensor)
plots_per_row = 2
total_rows = math.ceil(total_landmarks / plots_per_row)


fig, axs = plt.subplots(total_rows, plots_per_row, figsize=(15, total_rows * 5))
axs = axs.flatten()  # make it easier to index

for i, (_, row) in enumerate(landmarks_with_sensor.iterrows()):
    landmark_name = row['feature_name']

    #landmark location
    landmark_row = filtered_landmarks[filtered_landmarks['feature_name'] == landmark_name].iloc[0]
    landmark_location = (landmark_row['latitude'], landmark_row['longitude'])

    #find nearby pedestrian sensors
    nearby_sensors = filtered_pedestrian[filtered_pedestrian.apply(
        lambda x: geodesic(landmark_location, (x['latitude'], x['longitude'])).meters <= 400, axis=1
    )]

    ped_by_hour = nearby_sensors.groupby('hourday')['pedestriancount'].mean().reset_index()
    adjusted_spaces = row['adjusted_total_public_spaces']

    ax = axs[i]
    ax.plot(ped_by_hour['hourday'], ped_by_hour['pedestriancount'], marker='o', label='Pedestrian Count')
    ax.axhline(y=adjusted_spaces, color='red', linestyle='--', label='Adjusted Parking Spaces')
    ax.set_title(f"{landmark_name}", fontsize=12)
    ax.set_xlabel("Hour of Day")
    ax.set_ylabel("Pedestrian Count")
    ax.set_xticks(range(0, 24))
    ax.legend()
    ax.grid(True)

if total_landmarks % plots_per_row != 0:
    for j in range(total_landmarks, len(axs)):
        fig.delaxes(axs[j])

plt.tight_layout()
plt.show()

map_center = [-37.8136, 144.9631]
melb_map = folium.Map(location=map_center, zoom_start=14)

unique_sensors = filtered_pedestrian[['location_id', 'latitude', 'longitude']].drop_duplicates()
unique_sensors.head()

for _, row in landmark_access_summary.iterrows():
    #check if sensor data exists
    has_sensor = row['avg_pedestrian_count'] != 0

    marker_color = 'blue' if has_sensor else 'gray'

    avg_ped_display = 'no data' if not has_sensor else row['avg_pedestrian_count']
    adjusted_space = round(row['adjusted_total_public_spaces'], 1) if not pd.isna(row['adjusted_total_public_spaces']) else 'no data'
    peak_hour_display = f"<b>Peak Hour:</b> {row['peak_hour']}:00" if has_sensor and row['peak_hour'] != 'no data' else ""

    popup_text = f"""
    <b>{row['feature_name']}</b><br>
    Type: {row['theme']}<br>
    Nearby Public Car Parks: {row['nearby_public_carparks']}<br>
    Adjusted Parking Spaces: {adjusted_space}<br>
    Avg Pedestrian Count: {avg_ped_display}<br>
    {peak_hour_display}
    """

    folium.Marker(
        location=[row['latitude'], row['longitude']],
        popup=folium.Popup(popup_text, max_width=500),
        icon=folium.Icon(color=marker_color, icon='info-sign')
    ).add_to(melb_map)

    for _, row in unique_sensors.iterrows():
        folium.CircleMarker(
            location=[row['latitude'], row['longitude']],
            radius=4,
            color='green',
            fill=True,
            fill_opacity=0.7,
            popup=f"Sensor Location ID: {row['location_id']}"
        ).add_to(melb_map)

legend = MacroElement()
#legend._template = Template(legend_html)
melb_map.get_root().add_child(legend)

melb_map

map_center = [-37.8136, 144.9631]
combined_map = folium.Map(location=map_center, zoom_start=14)

#colors for demand risk
risk_colors = {
    'Low': 'green',
    'Moderate': 'orange',
    'High': 'red'
}

#add landmarks with demand risk colors (Only with valid sensor data)
valid_data = landmark_access_summary[landmark_access_summary['peak_pedestrian_count'] > 0]

for _, row in valid_data.iterrows():
    risk_level = row['demand_risk']
    marker_color = risk_colors.get(risk_level, 'gray')

    popup_text = f"""
    <b>{row['feature_name']}</b><br>
    Type: {row['theme']}<br>
    Peak Pedestrian Count: {row['peak_pedestrian_count']}<br>
    Adjusted Parking Spaces: {round(row['adjusted_total_public_spaces'], 1)}<br>
    Parking Demand Risk: <b>{risk_level}</b>
    """

    folium.Marker(
        location=[row['latitude'], row['longitude']],
        popup=folium.Popup(popup_text, max_width=400),
        icon=folium.Icon(color=marker_color, icon='info-sign')
    ).add_to(combined_map)

#add Bus Stops
for _, row in filtered_bus_stop.iterrows():
    folium.CircleMarker(
        location=[row['latitude'], row['longitude']],
        radius=3,
        color='orange',
        fill=True,
        fill_opacity=0.7,
        popup="Bus Stop"
    ).add_to(combined_map)

#add Tram Stops
for _, row in filtered_tram_stop.iterrows():
    folium.CircleMarker(
        location=[row['latitude'], row['longitude']],
        radius=3,
        color='purple',
        fill=True,
        fill_opacity=0.7,
        popup="Tram Stop"
    ).add_to(combined_map)

legend_html = """
<div style="
    position: fixed; 
    bottom: 50px; left: 50px; width: 230px; height: 160px; 
    background-color: white; border:2px solid grey; z-index:9999; font-size:14px;
    padding: 10px;">
    <b>Map Legend</b><br>
    <div style='display:inline-block;width:12px;height:12px;background-color:red;margin-right:10px;'></div> High Parking Demand Risk<br>
    <div style='display:inline-block;width:12px;height:12px;background-color:orange;margin-right:10px;'></div> Moderate Parking Demand Risk<br>
    <div style='display:inline-block;width:12px;height:12px;background-color:green;margin-right:10px;'></div> Low Parking Demand Risk<br>
    <div style='display:inline-block;width:12px;height:12px;background-color:gray;margin-right:10px;'></div> Unknown Parking Demand Risk<br>
    <div style='display:inline-block;width:12px;height:12px;background-color:orange;margin-right:10px;'></div> Bus Stops<br>
    <div style='display:inline-block;width:12px;height:12px;background-color:purple;margin-right:10px;'></div> Tram Stops
</div>
"""

legend = MacroElement()
legend._template = Template(legend_html)
combined_map.get_root().add_child(legend)

combined_map

risk_groups = landmark_access_summary.groupby('demand_risk')['feature_name'].apply(list)

markdown_text = "### Parking Demand Summary\n\n"

# Define risk order to control order in markdown
risk_order = ["High", "Moderate", "Low"]

for risk_level in risk_order:
    landmarks = risk_groups.get(risk_level, [])
    
    markdown_text += f"#### {risk_level} Risk\n"
    
    if landmarks:
        for landmark in landmarks:
            markdown_text += f"- {landmark}\n"
    else:
        markdown_text += "- None\n"
    
    markdown_text += "\n"

# Show markdown result
print(markdown_text)

### Parking Demand Summary

#### High Risk
- Carlton Primary School
- Kensington Primary School

#### Moderate Risk
- The Royal Victorian Eye & Ear Hospital
- Kangan Batman Tafe
- University of Melbourne (VCA and Music)

#### Low Risk
- Carlton Gardens Primary School
- RMIT University
- University of Melbourne
- Royal Dental Hospital
- State Library Victoria

high_risk_landmarks = landmark_access_summary[landmark_access_summary['demand_risk'] == 'High']

# For each high-risk landmark, find nearby bus and tram stops
for _, landmark in high_risk_landmarks.iterrows():
    landmark_name = landmark['feature_name']
    landmark_location = (landmark['latitude'], landmark['longitude'])

    # Find nearby bus stops within 400m
    nearby_bus = filtered_bus_stop[filtered_bus_stop.apply(
        lambda x: geodesic(landmark_location, (x['latitude'], x['longitude'])).meters <= 400, axis=1
    )]

    # Find nearby tram stops within 400m
    nearby_tram = filtered_tram_stop[filtered_tram_stop.apply(
        lambda x: geodesic(landmark_location, (x['latitude'], x['longitude'])).meters <= 400, axis=1
    )]

    print(f"{landmark_name}")
    print(f"- Nearby Bus Stops: {len(nearby_bus)}")
    print(f"- Nearby Tram Stops: {len(nearby_tram)}\n")

Carlton Primary School
- Nearby Bus Stops: 17
- Nearby Tram Stops: 10

Kensington Primary School
- Nearby Bus Stops: 5
- Nearby Tram Stops: 0

	theme	sub_theme	feature_name	co_ordinates
0	Place of Worship	Church	St Francis Church	-37.8118847831837, 144.962422614541
1	Place of Worship	Church	St James Church	-37.8101281201969, 144.952468571683
2	Place of Worship	Church	St Mary's Anglican Church	-37.8031663672997, 144.953761537074
3	Place of Worship	Church	Scots Church	-37.8145687802664, 144.96855105335
4	Place of Worship	Church	St Michael's Uniting Church	-37.8143851324913, 144.969174036096

	census_year	block_id	property_id	base_property_id	building_address	clue_small_area	parking_type	parking_spaces	longitude	latitude	location
0	2019	57	101213	101213	134-144 Bourke Street MELBOURNE 3000	Melbourne (CBD)	Private	30	144.968839	-37.812035	-37.81203485275635, 144.9688385527
1	2019	57	101219	101219	100-116 Bourke Street MELBOURNE 3000	Melbourne (CBD)	Private	68	144.969761	-37.811727	-37.81172679558099, 144.96976088474997
2	2019	58	101231	101231	40-50 Bourke Street MELBOURNE 3000	Melbourne (CBD)	Residential	26	144.971692	-37.811371	-37.81137105206584, 144.9716919528
3	2019	58	101232	101232	32-38 Bourke Street MELBOURNE 3000	Melbourne (CBD)	Private	5	144.971978	-37.811361	-37.81136084732718, 144.97197812345
4	2019	58	105659	105659	16-22 Liverpool Street MELBOURNE 3000	Melbourne (CBD)	Residential	85	144.971543	-37.811060	-37.81105967715, 144.97154296349532

	id	location_id	sensing_date	hourday	direction_1	direction_2	pedestriancount	sensor_name	location
0	72120220515	72	2022-05-15	1	104	172	276	ACMI_T	-37.81726338, 144.96872809
1	471720240917	47	2024-09-17	17	1273	894	2167	Eli250_T	-37.81258467, 144.9625775
2	172320211101	17	2021-11-01	23	8	6	14	Col15_T	-37.81362543, 144.97323591
3	171820230726	17	2023-07-26	18	267	383	650	Col15_T	-37.81362543, 144.97323591
4	24820250405	24	2025-04-05	8	213	218	431	Col620_T	-37.81887963, 144.95449198

	geo_point_2d	geo_shape	addresspt1	addressp_1	asset_clas	asset_type	objectid	str_id	addresspt	asset_subt	model_desc	mcc_id	roadseg_id	descriptio	model_no
0	-37.80384165792465, 144.93239283833262	{"coordinates": [144.93239283833262, -37.80384...	76.819824	357	Signage	Sign - Public Transport	355	1235255	570648	NaN	Sign - Public Transport 1 Panel	1235255	21673	Sign - Public Transport 1 Panel Bus Stop Type 13	P.16
1	-37.81548699581418, 144.9581794249902	{"coordinates": [144.9581794249902, -37.815486...	21.561304	83	Signage	Sign - Public Transport	600	1231226	548056	NaN	Sign - Public Transport 1 Panel	1231226	20184	Sign - Public Transport 1 Panel Bus Stop Type 8	P.16
2	-37.81353897396532, 144.95728334230756	{"coordinates": [144.95728334230756, -37.81353...	42.177187	207	Signage	Sign - Public Transport	640	1237092	543382	NaN	Sign - Public Transport 1 Panel	1237092	20186	Sign - Public Transport 1 Panel Bus Stop Type 8	P.16
3	-37.82191394843844, 144.95539345270072	{"coordinates": [144.95539345270072, -37.82191...	15.860434	181	Signage	Sign - Public Transport	918	1232777	103975	NaN	Sign - Public Transport 1 Panel	1232777	22174	Sign - Public Transport 1 Panel Bus Stop Type 8	P.16
4	-37.83316401267591, 144.97443745130263	{"coordinates": [144.97443745130263, -37.83316...	0.000000	0	Signage	Sign - Public Transport	1029	1271914	0	NaN	Sign - Public Transport 1 Panel	1271914	22708	Sign - Public Transport 1 Panel Bus Stop Type 8	P.16

	geo_point_2d	geo_shape	descriptio	name
0	-37.78861331868605, 144.93461561782556	{"coordinates": [[[[144.934525400489, -37.7886...	<center><table><tr><th colspan='2' align='cent...	kml_3
1	-37.819185576253524, 144.9610352542915	{"coordinates": [[[[144.96099441333, -37.81917...	<center><table><tr><th colspan='2' align='cent...	kml_5
2	-37.81837971233182, 144.95945259116502	{"coordinates": [[[[144.959343869512, -37.8182...	<center><table><tr><th colspan='2' align='cent...	kml_6
3	-37.81440385390417, 144.9702510191494	{"coordinates": [[[[144.969150077924, -37.8147...	<center><table><tr><th colspan='2' align='cent...	kml_7
4	-37.816738613153454, 144.9699088149105	{"coordinates": [[[[144.970082905454, -37.8167...	<center><table><tr><th colspan='2' align='cent...	kml_8

Import Libraries¶

Loading data through APIs V2.1 from CoM open data portal¶

Previewing the Raw Datasets¶

Data Preprocessing¶

Landmarks and places of interest, including schools, theatres, health services, sports facilities, places of worship, galleries and museums¶

Off-street car parks with capacity and type¶

Pedestrian Counting System (counts per hour)¶

Bus stops¶

Tram tracks¶

Data Integration¶

Linking landmarks with nearby public parking¶

Linking landmarks with pedestrian data¶

Combining parking and pedestrian data for each landmark¶

Check for shared or overlapping car parks¶

Combining public transport data for each landmark¶

Shared parking space adjustment¶

Parking Demand Analysis¶

Parking Demand Risk Classification¶

Pedestrian Demand vs Parking Capacity Analysis¶

Key Insights:¶

Data visualisation¶

Interactive Map of landmark accessibility¶

What the Map Shows:¶

Parking Demand and Public Transport Access in Melbourne¶

What This Map Shows¶

Key insight¶

Parking demand summary¶

Public Transport Access Near High Parking Demand Landmarks¶

What this step does:¶

Key insight:¶

Conclusion¶

Recommendations¶

	theme	sub_theme	feature_name	latitude	longitude
0	Education Centre	Primary Schools	Carlton Gardens Primary School	-37.802095	144.969406
1	Education Centre	Secondary Schools	Melbourne Grammar School	-37.834256	144.976285
2	Health Services	Private Hospital	Epworth Freemasons Hospital : Medical Centre	-37.809344	144.982337
3	Health Services	Public Hospital	Royal Womens Hospital	-37.798889	144.954897
4	Health Services	Public Hospital	Royal Childrens Hospital	-37.794206	144.950048

	latitude	longitude	building_address	clue_small_area	parking_spaces	parking_type
0	-37.846853	144.980378	553 St Kilda Road MELBOURNE VIC 3004	Melbourne (Remainder)	183	Commercial
1	-37.846570	144.982816	Alfred Hospital 23-99 Commercial Road PRAHRAN ...	Melbourne (Remainder)	1729	Commercial
2	-37.844593	144.979288	517 St Kilda Road MELBOURNE VIC 3004	Melbourne (Remainder)	8	Commercial
3	-37.837746	144.977044	415-421 St Kilda Road MELBOURNE VIC 3004	Melbourne (Remainder)	98	Commercial
4	-37.829090	144.970686	312-318 St Kilda Road SOUTHBANK VIC 3006	Southbank	240	Commercial

	location_id	sensing_date	hourday	pedestriancount	latitude	longitude
1	47	2024-09-17	17	2167	-37.812585	144.962578
4	24	2025-04-05	8	431	-37.818880	144.954492
5	54	2024-02-24	3	18	-37.804024	144.963084
6	50	2025-03-03	4	1	-37.798082	144.967210
7	143	2025-05-08	0	71	-37.821728	144.955570

	feature_name	avg_pedestrian_count	peak_hour	peak_pedestrian_count	pedestrian_display
0	Carlton Gardens Primary School	181.88	20	436	181.88
1	Melbourne Grammar School	0.00	no data	0	no data
2	Epworth Freemasons Hospital : Medical Centre	0.00	no data	0	no data
3	Royal Womens Hospital	0.00	no data	0	no data
4	Royal Childrens Hospital	0.00	no data	0	no data

	feature_name	theme	latitude	longitude	nearby_public_carparks	total_public_spaces	parking_addresses	avg_pedestrian_count	peak_hour	peak_pedestrian_count	pedestrian_display	nearby_bus_stops	nearby_tram_tracks	adjusted_total_public_spaces	parking_per_peak_person	demand_risk
15	Carlton Primary School	Education Centre	-37.795908	144.970147	2	369	[368-386 Lygon Street CARLTON VIC 3053, 6-30 T...	223.98	13	448	223.98	17	10	184.5	0.410913	High
19	Kensington Primary School	Education Centre	-37.792524	144.927109	0	0	[]	80.48	12	168	80.48	5	0	0.0	0.000000	High

Risk Level	Landmarks
High	- Carlton Primary School - Kensington Primary School
Moderate	- The Royal Victorian Eye & Ear Hospital - Kangan Batman Tafe - University of Melbourne (VCA and Music)
Low	- Carlton Gardens Primary School - RMIT University - University of Melbourne - Royal Dental Hospital